package com.itcc.dao.impl;

import com.itcc.dao.OrdersDao;
import com.itcc.entity.Address;
import com.itcc.entity.Item;
import com.itcc.entity.Orders;
import com.itcc.entity.Product;
import com.itcc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class OrderDaoImpl implements OrdersDao {
    @Override
    public void insertOrders(Orders order) throws SQLException {

        QueryRunner queryRunner=new QueryRunner(C3P0Utils.getDataSource());

        String sql="insert into orders(o_id,a_id,u_id,o_count,o_state,o_time) values(?,?,?,?,?,?);";

        queryRunner.update(sql,order.getOid(),order.getAid(),order.getUid(),order.getOcount(),order.getOstate(),order.getOtime());
    }

    @Override
    public void insertItems(List<Item> items) throws SQLException {

        QueryRunner queryRunner=new QueryRunner(C3P0Utils.getDataSource());
        Object[][] params=new Object[items.size()][];
        String sql="insert into item (o_id,p_id,i_count,i_num) values(?,?,?,?);";

        for (int i = 0; i < items.size(); i++) {
            Item item=items.get(i);
            params[i]=new Object[]{item.getOid(),item.getPid(),item.getIcount(),item.getInum()};
        }

        queryRunner.batch(sql,params);
    }

    @Override
    public List<Orders> selectOrdersByUid(int uid) throws SQLException, InvocationTargetException, IllegalAccessException {

        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select o.o_id as oid, o.u_id as uid, o.a_id as aid, " +
                "o.o_count as ocount, o.o_time as otime, o.o_state as ostate, " +
                "a.a_name as aname, a.a_phone as aphone, a.a_detail as adetail, " +
                "a.a_state as astate from address a join orders o on a.a_id = o.a_id " +
                "where o.u_id = ?;";

        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), uid);

        if (list == null) {
            return null;
        }

        List<Orders> ordersList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            Orders orders = new Orders();
            Address address = new Address();

            // 确保使用正确的键名
            orders.setOid((String) map.get("o_id"));
            orders.setUid((Integer) map.get("u_id") );
            orders.setAid((Integer) map.get("a_id"));
            orders.setOcount( (BigDecimal) map.get("o_count") );
            // 处理日期转换
            Object otime = map.get("o_time");
            Date date = null;
            if (otime != null) {
                if (otime instanceof Timestamp) {
                    date = new Date(((Timestamp) otime).getTime());
                } else if (otime instanceof LocalDateTime) {
                    date = Date.from(((LocalDateTime) otime).atZone(ZoneId.systemDefault()).toInstant());
                }
            }
            orders.setOtime(date);
            orders.setOstate((Integer) map.get("o_state"));

            address.setAid( (Integer) map.get("a_id") );
            address.setAname( map.get("a_name").toString());
            address.setAphone((String) map.get("a_phone"));
            address.setAdetail((String) map.get("a_detail") );
            address.setAstate(Integer.parseInt((String) map.get("a_state")));


            orders.setAddress(address);
            ordersList.add(orders);
        }

        return ordersList;
    }

    @Override
    public Orders selectOrderByOid(String oid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

        String sql = "select o.o_id as oid, o.u_id as uid, o.a_id as aid, " +
                "o.o_count as ocount, o.o_time as otime, o.o_state as ostate, " +
                "a.a_name as aname, a.a_phone as aphone, a.a_detail as adetail, " +
                "a.a_state as astate from address a join orders o on a.a_id = o.a_id " +
                "where o.o_id = ?;";

        Map<String, Object> map = queryRunner.query(sql, new MapHandler(), oid);

        if(map == null){
            return null;
        }
        Orders orders = new Orders();
        Address address = new Address();

        orders.setOid(map.get("o_id") != null ? (String) map.get("o_id") : null);
        orders.setUid(map.get("u_id") != null ? (Integer) map.get("u_id") : 0);

        orders.setOcount(map.get("o_count") != null ? (BigDecimal) map.get("o_count") : BigDecimal.ZERO);

        // 处理日期转换
        Object otime = map.get("o_time");
        Date date = null;
        if (otime != null) {
            if (otime instanceof Timestamp) {
                date = new Date(((Timestamp) otime).getTime());
            } else if (otime instanceof LocalDateTime) {
                date = Date.from(((LocalDateTime) otime).atZone(ZoneId.systemDefault()).toInstant());
            }
        }
        orders.setOtime(date);

        orders.setOstate((Integer)map.get("o_state") );

        address.setAid((Integer)map.get("a_id") );
        orders.setAid(address.getAid());
        address.setAname(map.get("a_name") != null ? map.get("a_name").toString() : null);
        address.setAphone(map.get("a_phone") != null ? (String) map.get("a_phone") : null);
        address.setAdetail(map.get("a_detail") != null ? (String) map.get("a_detail") : null);
        address.setAstate(map.get("a_state") != null ? Integer.parseInt((String) map.get("a_state")) : 0);

        orders.setAddress(address);

        return orders;
    }

    @Override
    public List<Item> selectItemsByOid(String oid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

        String sql = "select p.p_id as pid, p.t_id as tid, p.p_name as pname, p.p_time as ptime, " +
                "p.p_image as pimage, p.p_state as pstate, p.p_info as pinfo, p.p_price as pprice, " +
                "i.o_id as oid, i.i_id as iid, i.i_count as icount, i.i_num as inum " +
                "from product p join item i on p.p_id = i.p_id where i.o_id = ?;";

        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), oid);

        if (list == null) {
            return null;
        }

        List<Item> items = new ArrayList<>();

        for (Map<String, Object> map : list) {
            Item item = new Item();
            Product product = new Product();

            product.setPid( (Integer) map.get("p_id") );

            product.setTid( (Integer) map.get("t_id") );
            product.setPname(map.get("p_name").toString() );

            // 处理日期转换
            Object ptime = map.get("p_time");

            if (ptime != null) {
                product.setPtime((Date) ptime);
            }
            product.setPimage(map.get("p_image") != null ? map.get("p_image").toString() : null);
            product.setPstate(map.get("p_state") != null ? (Integer) map.get("p_state") : 0);
            product.setPinfo(map.get("p_info") != null ? map.get("p_info").toString() : null);
            product.setPprice( (BigDecimal) map.get("p_price") );

            item.setOid( (String) map.get("o_id") );
            item.setIid( product.getPid() );
            item.setIcount((BigDecimal) map.get("i_count") );
            item.setInum((Integer) map.get("i_num") );

            item.setProduct(product);
            items.add(item);

        }
        return items;
    }

    @Override
    public void updateStatusByOid(String oid) throws SQLException {

        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql="update orders set o_state=? where o_id=?;";
        int updatedRows = queryRunner.update(sql, 2, oid);

    }

    @Override
    public List<Orders> findAllOrders() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT o_id as oid, u_id as uid, a_id as aid, " +
                "o_count as ocount, o_time as otime, o_state as ostate FROM orders;";

        // 使用MapListHandler来处理查询结果
        List<Map<String, Object>> maps = queryRunner.query(sql, new MapListHandler());
        List<Orders> list = new ArrayList<>();
        // 遍历maps，手动创建Orders对象并进行类型转换
        for (Map<String, Object> map : maps) {
            Orders order = new Orders();
            order.setOid((String) map.get("o_id"));
            order.setUid((Integer) map.get("u_id"));
            order.setAid((Integer) map.get("a_id"));
            order.setOcount((BigDecimal) map.get("o_count"));
            // 处理日期转换
            Object otime = map.get("o_time");
            Date date = null;
            if (otime != null) {
                if (otime instanceof Timestamp) {
                    date = new Date(((Timestamp) otime).getTime());
                } else if (otime instanceof LocalDateTime) {
                    date = Date.from(((LocalDateTime) otime).atZone(ZoneId.systemDefault()).toInstant());
                }
            }
            order.setOtime(date);
            order.setOstate((Integer) map.get("o_state"));
            list.add(order);
        }
        return list;
    }

}
